package org.dandelion.goalTest.util;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import org.dandelion.goalTest.bean.Course;
import org.dandelion.goalTest.bean.Student;
import org.dandelion.goalTest.bean.Student_Course;


public class DBHelper {
    private static final String DATABASE_NAME = "toolbox";
    private static final int DATABASE_VERSION = 1;
    private static final String STUDENT_TABLE_NAME = "student";
    private static final String STUDENT_COURSE_TABLE_NAME = "student_course";

    private static final String COURSE_TABLE_NAME = "course";
    //    public static final String ID ="_id";
//    public static final String TYPE= "type" ;
//    public static final String MONEY = "money" ;
//    public static final String DESC = "description";
    private DBOpenHelper helper;
    private SQLiteDatabase db;

    private static class DBOpenHelper extends SQLiteOpenHelper {
        private static final String CREATE_TABLE =
                "create table " + STUDENT_TABLE_NAME + "(" + "stu_id" + " integer primary key, "
                        + "name" + " text not null);";

        private static final String CREATE_TABLE1 =
                "create table " + COURSE_TABLE_NAME + "(" + "course_id" + " varchar(10) primary key, "
                        + "name" + " text not null, " + "type" + " boolean not null," + " credit integer not null);";

        private static final String CREATE_TABLE3 =
                "create table " + STUDENT_COURSE_TABLE_NAME + "( _id integer primary key autoincrement, " + "stu_id" + " integer, "
                        + "course_id" + " varchar(10) not null, " + "goal" + " float not null);";

        public DBOpenHelper(@Nullable Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            System.out.println(CREATE_TABLE);
            System.out.println(CREATE_TABLE1);
            System.out.println(CREATE_TABLE3);
            db.execSQL(CREATE_TABLE);
            db.execSQL(CREATE_TABLE1);
            db.execSQL(CREATE_TABLE3);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            db.execSQL("drop table if exists " + STUDENT_TABLE_NAME);
            db.execSQL("drop table if exists " + COURSE_TABLE_NAME);
            db.execSQL("drop table if exists " + STUDENT_COURSE_TABLE_NAME);
            onCreate(db);
        }
    }

    public DBHelper(Context context) {
        helper = new DBOpenHelper(context);
        db = helper.getWritableDatabase();
    }

    public void insertIntoStudent(Student student) {
        ContentValues contentValues = new ContentValues();

        contentValues.put("stu_id", student.getStu_id());
        contentValues.put("name", student.getName());

        db.insert(STUDENT_TABLE_NAME, null, contentValues);
    }

    public void insertIntoCourse(Course course) {
        ContentValues contentValues = new ContentValues();

        contentValues.put("course_id", course.getCourse_id());
        contentValues.put("name", course.getName());
        contentValues.put("type", course.getType());
        contentValues.put("credit", course.getCredit());

        db.insert(COURSE_TABLE_NAME, null, contentValues);
    }

    public void insertIntoStudent_Course(Student_Course student_course) {
        ContentValues contentValues = new ContentValues();

        contentValues.put("course_id", student_course.getCourse_id());
        contentValues.put("stu_id", student_course.getStu_id());
        contentValues.put("goal", student_course.getGoal());

        db.insert(STUDENT_COURSE_TABLE_NAME, null, contentValues);
    }


    public Cursor selectStudent_Course(int stu_id) {
        /*待测试
         *
         * TODO:根据学号查询所有课程成绩
         *
         * */
        Cursor cursor = null;
        String sql = "select _id, course.course_id, name, goal, type,credit from course, student_course" +
                " where student_course.stu_id = ? and student_course.course_id = course.course_id;";
        try {
            cursor = db.rawQuery(sql, new String[]{String.valueOf(stu_id)});
            return cursor;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


}
